package com.sinog.biz.schedule;

import com.alibaba.fastjson.JSONObject;
import com.sinog.biz.BizApplication;
import com.sinog.biz.config.ThreadPoolConfig;
import com.sinog.biz.service.api.DataTransmissionService;
import com.sinog.biz.util.DataSourceCheckUtils;
import com.sinog.core.domain.PrisonerFx;
import com.sinog.core.domain.PrisonerMain;
import com.sinog.core.domain.PrisonerSr;
import com.sinog.core.domain.PrisonerZn;
import com.sinog.core.domain.UserInterFaceEntity;
import com.sinog.core.model.MultimediaInfo;
import com.sinog.core.util.CollectionUtils;
import com.sinog.core.util.Hex;
import com.sinog.core.utilbean.Constants;
import com.sinog.core.utilbean.IdGen;
import com.sinog.core.utilbean.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.regex.Pattern;

/**
 * @desc 预安置人员数据录入，方式2：中间库方式 - (监狱、社区矫正、看守所) - oracle数据库版本
 * @author lzq
 * @date 2020-12-01 2:36 下午
 */
@Slf4j
@Component
@Configuration
@ConditionalOnProperty(name = "gkzx.azbj.outerInterface.db.oracle.open", havingValue = "true")
public class ShadowForOracleSchedule {

    @Autowired
    private ThreadPoolConfig threadPoolConfig;
    private static final Pattern PATTERN = Pattern.compile("\\d{9}");
    private static final String DJ_PRINUMS = "dj_priNums";
    /**
     * 中间库数据源
     */
    private JdbcTemplate oracleJdbcTemplate;
    /**
     * 安置帮教库数据源
     */
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private DataTransmissionService dataTransmissionService;
    @Resource
    private StringRedisTemplate stringRedisTemplate;

    /**
     * 定时从中间库拉取数据保存到本地库(生产上每天晚11点跑：0 0 23 * * ?)，本地测试每分钟执行一次：0 0/1 * * * ?
     */
    @Scheduled(cron = "${gkzx.azbj.outerInterface.db.oracle.cron}")
    private void predictWarningShadowTask() {
        log.info("\n>>>>>>>>>>>starting......从中间库同步预安置人员数据<<<<<<<<<<");
        SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
        ApplicationContext applicationContext = BizApplication.applicationContext;
        long startTime = System.currentTimeMillis();
        if (!ObjectUtils.allNotNull(oracleJdbcTemplate)) {
            oracleJdbcTemplate = (JdbcTemplate) applicationContext.getBean("oracleJdbcTemplate");
        }
        if (!ObjectUtils.allNotNull(jdbcTemplate)) {
            jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        }
        //检测全部数据源是否可用
        boolean allRunning = DataSourceCheckUtils.checkRunningStatus(oracleJdbcTemplate,jdbcTemplate);
        if(true){
            //1、提供sql：查询---> 中间库PRISONER_MAIN表,查询update_time为昨天的全部记录,返回昨日记录
            String priSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_MAIN where update_time >= TRUNC(SYSDATE - 1) and update_time <= TRUNC(SYSDATE - 0)";
            String fxbSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_FX where update_time >= TRUNC(SYSDATE - 1) and update_time <= TRUNC(SYSDATE - 0)";
            String srbSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_SR where update_time >= TRUNC(SYSDATE - 1) and update_time <= TRUNC(SYSDATE - 0)";
            String znbSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_ZN where update_time >= TRUNC(SYSDATE - 1) and update_time <= TRUNC(SYSDATE - 0)";
            //1、提供sql：查询---> 中间库PRISONER_MAIN表,查询update_time为昨天的全部记录,返回昨日记录
            String inPriSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_MAIN where create_time >= TRUNC(SYSDATE - 8) and create_time <= TRUNC(SYSDATE - 7)";
            String inFxbSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_FX where create_time >= TRUNC(SYSDATE - 1) and create_time <= TRUNC(SYSDATE - 0)";
            String inSrbSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_SR where create_time >= TRUNC(SYSDATE - 1) and create_time <= TRUNC(SYSDATE - 0)";
            String inZnbSql = "select pri_num from " +
                    "DATA_SWAP.PRISONER_ZN where create_time >= TRUNC(SYSDATE - 1) and create_time <= TRUNC(SYSDATE - 0)";
            long startTime1 = System.currentTimeMillis();
            // 1.1、执行sql：返回---> update_time为昨天的记录
            oracleJdbcTemplate.queryForList(priSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            oracleJdbcTemplate.queryForList(fxbSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            oracleJdbcTemplate.queryForList(srbSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            oracleJdbcTemplate.queryForList(znbSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            // 1.1、执行sql：返回---> create_time为昨天的记录
            oracleJdbcTemplate.queryForList(inPriSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            oracleJdbcTemplate.queryForList(inFxbSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            oracleJdbcTemplate.queryForList(inSrbSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            oracleJdbcTemplate.queryForList(inZnbSql).forEach(map -> {
                if (!map.isEmpty() && null != map.get("pri_num")) {
                    stringRedisTemplate.opsForSet().add(DJ_PRINUMS, (String) map.get("pri_num"));
                }
            });
            // 1.2 获取set集合中的所有数据
            List<String> djPriNums = new ArrayList<>(stringRedisTemplate.opsForSet().members(DJ_PRINUMS));
            Map<String, Object> args = new HashMap<>(16);
            List<Map<String, Object>> mainLists = new ArrayList<>();
            if (CollectionUtils.isNotEmpty(djPriNums)) {
                args.put("priNums", djPriNums);
                // 1.3 提供sql: 根据redis中获取的priNum记录数,从数据库中查询返回接口
                String mainSql = "select * from (select * from DATA_SWAP.PRISONER_MAIN where pri_num in (:priNums)) where rownum <= 300";
                NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(oracleJdbcTemplate);
                // 2 执行查询: 查询所有符合条件的集合
                mainLists = givenParamJdbcTemp.queryForList(mainSql, args);
            }
            Date date1 = new Date();
            long endTime1 = System.currentTimeMillis();
            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>查询300条记录,执行耗时,耗时:{}", (endTime1 - startTime1) / 1000 / 60);
            CountDownLatch countDownLatch = new CountDownLatch(mainLists.size());
            //3、遍历每一条主表数据，并且根据【对方的犯罪编号】查询出相关的服刑表、社会关系表、子女表、多媒体信息表
            try {
                for (Map<String, Object> mainMaps : mainLists) {
                    String priNum = (String) mainMaps.get("pri_num");
                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>循环前执行的format:{}", format1.format(date1));
                    // 判断,如果此PRI_NUM做过处理,则跳出循环
                    if (stringRedisTemplate.opsForHash().
                            entries(format1.format(date1)).containsValue(priNum)) {
                        countDownLatch.countDown();
                        log.error("continue,执行一次,本轮还剩:{}次", countDownLatch.getCount());
                        continue;
                    }
                    threadPoolConfig.taskExecutor().execute(() -> {
                        try {
                            long startTime2 = System.currentTimeMillis();
                            //3.1、取出当前main表数据map
                            //3.2、定义PrisonerMain
                            PrisonerMain prisonerMain = new PrisonerMain();
                            //3.3、取出map中prisonerMain属性
                            String prinum = (String) mainMaps.get("pri_num");
                            String azbjPrinum1 = (String) mainMaps.get("azbj_prinum");
                            String account = (String) mainMaps.get("account");
                            String pname = (String) mainMaps.get("p_name");
                            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>中间库对接-服刑人员姓名:{}", pname);
                            String usedname = (String) mainMaps.get("used_name");
                            String sex = (String) mainMaps.get("sex");
                            String nationality = (String) mainMaps.get("nationality");
                            // 民族从01开始
                            if (Constants.Number.NUM_ONE == nationality.length()) {
                                nationality = "0" + nationality;
                            }
                            String idType = (String) mainMaps.get("idType");
                            String idnum = (String) mainMaps.get("id_num");
                            Date age = (Date) mainMaps.get("age");
                            String education = (String) mainMaps.get("education");
                            Date addtime = (Date) mainMaps.get("addtime");
                            String priAddr = (String) mainMaps.get("pri_addr");
                            String nativepro = (String) mainMaps.get("nativepro");
                            String nativecity = (String) mainMaps.get("nativecity");
                            String nativecou = (String) mainMaps.get("nativecou");
                            String nativevill = (String) mainMaps.get("nativevill");
                            // 黑龙江传输文字,如果不为空,且校验失败则赋值空
                            if (!StringUtils.isEmpty(nativevill) && !PATTERN.matcher(nativevill).matches()) {
                                nativevill = "";
                            }
                            String nativenum = (String) mainMaps.get("nativenum");
                            String placepro = (String) mainMaps.get("placepro");
                            String placecity = (String) mainMaps.get("placecity");
                            String placecou = (String) mainMaps.get("placecou");
                            String placevill = (String) mainMaps.get("placevill");
                            if (!StringUtils.isEmpty(placevill) && !PATTERN.matcher(placevill).matches()) {
                                placevill = "";
                            }
                            String placenum = (String) mainMaps.get("placenum");
                            String arrestpro = (String) mainMaps.get("arrestpro");
                            String arrestcity = (String) mainMaps.get("arrestcity");
                            String arrestcou = (String) mainMaps.get("arrestcou");
                            String arrestvill = (String) mainMaps.get("arrestvill");
                            if (!StringUtils.isEmpty(arrestvill) && !PATTERN.matcher(arrestvill).matches()) {
                                arrestvill = "";
                            }
                            String arrestnum = (String) mainMaps.get("arrestnum");
                            String investigatepro = (String) mainMaps.get("investigate_pro");
                            String investigatecity = (String) mainMaps.get("investigate_city");
                            String investigatecou = (String) mainMaps.get("investigate_cou");
                            String investigatevill = (String) mainMaps.get("investigate_vill");
                            if (!StringUtils.isEmpty(investigatevill) && !PATTERN.matcher(investigatevill).matches()) {
                                investigatevill = "";
                            }
                            String investigatenum = (String) mainMaps.get("investigate_num");
                            //3.4、封装实体类prisonerMain
                            prisonerMain.setCreateUser(account);
                            prisonerMain.setCreateTime(date1);
                            prisonerMain.setUpdateUser(account);
                            prisonerMain.setUpdateTime(date1);
                            prisonerMain.setPriNum(azbjPrinum1);
                            prisonerMain.setPName(pname);
                            prisonerMain.setUsedName(usedname);
                            prisonerMain.setSex(sex);
                            prisonerMain.setNationality(nationality);
                            prisonerMain.setIdType(idType);
                            prisonerMain.setIdNum(idnum);
                            prisonerMain.setAge(age);
                            prisonerMain.setEducation(education);
                            prisonerMain.setAddtime(addtime);
                            prisonerMain.setPriAddr(priAddr);
                            prisonerMain.setNativepro(nativepro);
                            prisonerMain.setNativecity(nativecity);
                            prisonerMain.setNativecou(nativecou);
                            prisonerMain.setNativevill(nativevill);
                            prisonerMain.setNativenum(nativenum);
                            prisonerMain.setPlacepro(placepro);
                            prisonerMain.setPlacecity(placecity);
                            prisonerMain.setPlacecou(placecou);
                            prisonerMain.setPlacevill(placevill);
                            prisonerMain.setPlacenum(placenum);
                            prisonerMain.setArrestpro(arrestpro);
                            prisonerMain.setArrestcity(arrestcity);
                            prisonerMain.setArrestcou(arrestcou);
                            prisonerMain.setArrestvill(arrestvill);
                            prisonerMain.setArrestnum(arrestnum);
                            prisonerMain.setInvestigatePro(investigatepro);
                            prisonerMain.setInvestigateCity(investigatecity);
                            prisonerMain.setInvestigateCou(investigatecou);
                            prisonerMain.setInvestigateVill(investigatevill);
                            prisonerMain.setInvestigateNum(investigatenum);
                            //3.5、定义sql：根据【对方犯罪编号】查询---> 中间库PRISONER_FX数据
                            String fxSql = "select * from DATA_SWAP.PRISONER_FX where pri_num =\'" + prinum + "\'";
                            //3.6、执行sql：返回---> 中间库PRISONER_FX表数据
                            List<Map<String, Object>> fxLists = oracleJdbcTemplate.queryForList(fxSql);
                            //如果服刑信息为空，则返回错误
                            if (fxLists.isEmpty()) {
                                log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>fxLists.isEmpty():{}", pname);
                                String sql5 = "update DATA_SWAP.PRISONER_MAIN set state='2' where pri_num=\'" + prinum + "\'";
                                String sb = "INSERT INTO PRISONER_MIDDLE_LOG (id,pri_num,code,create_time,update_time) ";
                                StringBuilder sql10 = new StringBuilder(sb);
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String format = simpleDateFormat.format(date1);
                                //服刑表信息不能为空
                                String code = "999991";
                                sql10.append("values (").append("\'").append(IdGen.strid()).append("\',\'").append(prinum).append("\',\'").append(code)
                                     .append("\',to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\'")
                                     .append("),to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\')").append(")");
                                oracleJdbcTemplate.execute(sql5);
                                oracleJdbcTemplate.execute(sql10.toString());
                                // 去缓存中移除指定value
                                stringRedisTemplate.opsForSet().remove(DJ_PRINUMS, prinum);
                                // 将执行过后的结果存放入redis
                                stringRedisTemplate.opsForHash().put(format1.format(date1), prinum, prinum);
                                return;
                            }
                            //3.7、定义PrisonerFx
                            PrisonerFx prisonerFx = null;
                            for (Map<String, Object> fxList : fxLists) {
                                prisonerFx = new PrisonerFx();
                                //3.8 取出map中prisonerFx属性
                                String critype = (String) fxList.get("Critype");
                                String precri = (String) fxList.get("Precri");
                                String addpenalty = (String) fxList.get("Addpenalty");
                                String jinengtype = (String) fxList.get("jinengtype");
                                String pritime = (String) fxList.get("Pritime");
                                String arrjg = (String) fxList.get("arr_jg");
                                Date pribegin = (Date) fxList.get("Pri_begin");
                                Date pristop = (Date) fxList.get("Pri_stop");
                                String aftype = (String) fxList.get("Aftype");
                                String bqzy = (String) fxList.get("bqzy");
                                String fzlx = (String) fxList.get("fzlx");
                                String canji = (String) fxList.get("canji");
                                //3.9 封装实体类prisonerFx
                                prisonerFx.setCreateUser(account);
                                prisonerFx.setCreateTime(date1);
                                prisonerFx.setUpdateUser(account);
                                prisonerFx.setUpdateTime(date1);
                                prisonerFx.setCritype(critype);
                                prisonerFx.setPrecri(precri);
                                prisonerFx.setAddpenalty(addpenalty);
                                prisonerFx.setJinengtype(jinengtype);
                                prisonerFx.setPritime(pritime);
                                prisonerFx.setArrJg(arrjg);
                                prisonerFx.setPriBegin(pribegin);
                                prisonerFx.setPriStop(pristop);
                                prisonerFx.setAftype(aftype);
                                prisonerFx.setBqzy(bqzy);
                                if (!StringUtils.isEmpty(critype) && StringUtils.isEmpty(fzlx)) {
                                    String[] critypes = critype.split(",");
                                    List<String> fzlxs = new ArrayList<>(16);
                                    for (String c : critypes) {
                                        //根据罪名查询对应的犯罪类型
                                        String fzlxTemp = (String) stringRedisTemplate.opsForHash().get("critypeAndFzlx", c);
                                        if (!fzlxs.contains(fzlxTemp)) {
                                            fzlxs.add(fzlxTemp);
                                        }
                                    }
                                    String temp = "";
                                    for (String f : fzlxs) {
                                        if (!StringUtils.isEmpty(temp)) {
                                            temp = new StringBuilder(16).append(temp).append(",").append(f).toString();
                                        } else {
                                            temp = f;
                                        }
                                    }
                                    fzlx = temp;
                                }
                                prisonerFx.setFzlx(fzlx);
                                prisonerFx.setCanji(canji);
                            }
                            //3.10、定义sql：根据【对方犯罪编号】查询---> 中间库PRISONER_SR数据
                            String srSql = "select * from DATA_SWAP.PRISONER_SR where pri_num =\'" + prinum + "\'";
                            //3.11、执行sql：返回---> 中间库PRISONER_SR表数据
                            List<Map<String, Object>> srLists = oracleJdbcTemplate.queryForList(srSql);
                            //3.12、定义PrisonerSr
                            List<PrisonerSr> prisonerSrs = new ArrayList<>(16);
                            for (Map<String, Object> srList : srLists) {
                                PrisonerSr prisonerSr = new PrisonerSr();
                                //3.13 取出map中PrisonerSr属性
                                String thename = (String) srList.get("thename");
                                String relation = (String) srList.get("relation");
                                Date srage = (Date) srList.get("age");
                                String danwei = (String) srList.get("danwei");
                                String tel = (String) srList.get("tel");
                                String mobile = (String) srList.get("mobile");
                                String addrpro = (String) srList.get("addrpro");
                                String addrcity = (String) srList.get("addrcity");
                                String addrcou = (String) srList.get("addrcou");
                                String addrvil = (String) srList.get("addrvil");
                                if (!StringUtils.isEmpty(addrvil) && !PATTERN.matcher(addrvil).matches()) {
                                    addrvil = "";
                                }
                                String addrdetail = (String) srList.get("addrdetail");
                                String idnumber = (String) srList.get("idnumber");
                                //针对黑龙江约定做特殊处理
                                if ("000000000000000".equals(idnumber)) {
                                    idnumber = "";
                                }
                                //3.14 封装实体类PrisonerSr
                                prisonerSr.setCreateUser(account);
                                prisonerSr.setCreateTime(date1);
                                prisonerSr.setUpdateUser(account);
                                prisonerSr.setUpdateTime(date1);
                                prisonerSr.setThename(thename);
                                prisonerSr.setRelation(relation);
                                prisonerSr.setAge(srage);
                                prisonerSr.setTel(!StringUtils.isEmpty(tel) && "无".equals(tel) ? "" : tel);
                                prisonerSr.setDanwei(danwei);
                                prisonerSr.setMobile(!StringUtils.isEmpty(mobile) && "无".equals(mobile) ? "" : mobile);
                                prisonerSr.setAddrpro(addrpro);
                                prisonerSr.setAddrcity(addrcity);
                                prisonerSr.setAddrcou(addrcou);
                                prisonerSr.setAddrvil(addrvil);
                                prisonerSr.setAddrdetail(addrdetail);
                                prisonerSr.setIdnumber(idnumber);
                                //3.15 添加PrisonerSr到list
                                prisonerSrs.add(prisonerSr);
                            }
                            //3.16、定义sql：根据【对方犯罪编号】查询---> 中间库PRISONER_ZN数据
                            String znSql = "select * from DATA_SWAP.PRISONER_ZN where pri_num =\'" + prinum + "\'";
                            //3.17、执行sql：返回---> 中间库PRISONER_ZN表数据
                            List<Map<String, Object>> znLists = oracleJdbcTemplate.queryForList(znSql);
                            //3.18、定义prisonerZn
                            List<PrisonerZn> prisonerZns = new ArrayList<>(16);
                            for (Map<String, Object> znList : znLists) {
                                PrisonerZn prisonerZn = new PrisonerZn();
                                //3.19 取出map中prisonerZn属性
                                String name = (String) znList.get("name");
                                String znsex = (String) znList.get("sex");
                                Date znage = (Date) znList.get("age");
                                String jianhuren = (String) znList.get("jianhuren");
                                String kunnan = (String) znList.get("kunnan");
                                String shixue = (String) znList.get("shixue");
                                String liulang = (String) znList.get("liulang");
                                String fanzui = (String) znList.get("fanzui");
                                String znsfId = (String) znList.get("znsf_id");
                                String znnativepro = (String) znList.get("nativepro");
                                String znnativecity = (String) znList.get("nativecity");
                                String znnativecou = (String) znList.get("nativecou");
                                String znnativevill = (String) znList.get("nativevill");
                                if (!StringUtils.isEmpty(znnativevill) && !PATTERN.matcher(znnativevill).matches()) {
                                    znnativevill = "";
                                }
                                String znnativenum = (String) znList.get("nativenum");
                                String znplacepro = (String) znList.get("placepro");
                                String znplacecity = (String) znList.get("placecity");
                                String znplacecou = (String) znList.get("placecou");
                                String znplacevill = (String) znList.get("placevill");
                                if (!StringUtils.isEmpty(znplacevill) && !PATTERN.matcher(znplacevill).matches()) {
                                    znplacevill = "";
                                }
                                String znplacenum = (String) znList.get("placenum");
                                //3.20 封装实体类prisonerZn
                                prisonerZn.setCreateUser(account);
                                prisonerZn.setCreateTime(date1);
                                prisonerZn.setUpdateUser(account);
                                prisonerZn.setUpdateTime(date1);
                                prisonerZn.setName(name);
                                prisonerZn.setSex(znsex);
                                prisonerZn.setAge(znage);
                                prisonerZn.setJianhuren(jianhuren);
                                prisonerZn.setKunnan(kunnan);
                                prisonerZn.setShixue(shixue);
                                prisonerZn.setLiulang(liulang);
                                prisonerZn.setFanzui(fanzui);
                                prisonerZn.setZnsfId(znsfId);
                                prisonerZn.setNativepro(znnativepro);
                                prisonerZn.setNativecity(znnativecity);
                                prisonerZn.setNativecou(znnativecou);
                                prisonerZn.setNativevill(znnativevill);
                                prisonerZn.setNativenum(znnativenum);
                                prisonerZn.setPlacepro(znplacepro);
                                prisonerZn.setPlacecity(znplacecity);
                                prisonerZn.setPlacecou(znplacecou);
                                prisonerZn.setPlacevill(znplacevill);
                                prisonerZn.setPlacenum(znplacenum);
                                //3.21 添加prisonerZn到list
                                prisonerZns.add(prisonerZn);
                            }
                            //3.22、定义sql：根据【对方犯罪编号】查询---> 中间库PRISONER_MULTIMEDIA数据
                            String mediaSql = "select * from DATA_SWAP.PRISONER_MULTIMEDIA where pri_num =\'" + prinum + "\'";
                            //3.23、执行sql：返回---> 中间库PRISONER_MULTIMEDIA表数据
                            List<Map<String, Object>> mediaLists = oracleJdbcTemplate.queryForList(mediaSql);
                            //3.24、定义multimediaInfo
                            List<MultimediaInfo> multimediaInfos = new ArrayList<>(16);
                            for (Map<String, Object> mediaList : mediaLists) {
                                MultimediaInfo multimediaInfo = new MultimediaInfo();
                                //3.25 取出map中multimediaInfo属性
                                String taskId = (String) mediaList.get("task_id");
                                String imgstr = (String) mediaList.get("imgstr");
                                String filetype = (String) mediaList.get("filetype");
                                //3.26 封装实体类multimediaInfo
                                multimediaInfo.setTaskId(taskId);
                                multimediaInfo.setImgstr(imgstr);
                                multimediaInfo.setFiletype(filetype);
                                //3.27 添加multimediaInfo到list
                                multimediaInfos.add(multimediaInfo);
                            }
                            //3.28 定义sql：查询账号信息
                            String sqluser = "SELECT tu.account,tu.username,tu.orgid,tu.uid,tu.orgid as pid," + "(SELECT name from tbsys_department where id=tu.orgid) as pname," + "(SELECT name from tbsys_department where id=tu.orgid) as orgName," + "tu.departid,td.name AS departName,td.level," + "tp.name AS positionName,tu.roleid,tr.name AS roleName,tu.unCheckField as unCheckField " + "FROM `tbsys_user_interface` tu " + "JOIN `tbsys_department` td ON tu.departid = td.id " + "JOIN `tbsys_position` tp ON tu.position = tp.id " + "JOIN `tbsys_role` tr ON tu.roleid = tr.id " + "WHERE tu.account= " + "\'" + account + "\'";

                            //3.29、查询安置帮教系统中账号信息，并根据账号信息获取接口密钥
                            BeanPropertyRowMapper<UserInterFaceEntity> rowMapper1 = new BeanPropertyRowMapper<>(UserInterFaceEntity.class);
                            UserInterFaceEntity userInterface;
                            String signature;
                            long time = System.currentTimeMillis();
                            try {
                                userInterface = jdbcTemplate.queryForObject(sqluser, rowMapper1);
                                String uid = userInterface.getUid();
                                String key;
                                key = Hex.genAccessKey(uid);
                                //客户端自己生成密钥
                                signature = Hex.genSignature(key, String.valueOf(time), "/transmission/api/azbj/savePreArrangedPersonnel/");
                                //3.30、定义json对象，并添加下面节点
                                JSONObject jsonObject = new JSONObject();
                                jsonObject.put("time", String.valueOf(time));
                                jsonObject.put("sign", signature);
                                jsonObject.put("PrisonerMain", prisonerMain);
                                jsonObject.put("MultimediaInfo", multimediaInfos);
                                jsonObject.put("PrisonerFx",prisonerFx);
                                jsonObject.put("PrisonerSr", prisonerSrs);
                                jsonObject.put("PrisonerZn", prisonerZns);
                                jsonObject.put("user", userInterface);
                                //3.31、定义返回结果
                                Result result;
                                String flag;
                                log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>向数据库开始同步数据:azbjPrinum1:{},pName:{}", azbjPrinum1, pname);
                                if (!StringUtils.isEmpty(azbjPrinum1)) {
                                    //3.32-1、更新,如果主表中的安置帮教犯罪编号不为空，说明是更新
                                    result = dataTransmissionService.updatePreArrangedPersonnel(JSONObject.toJSON(jsonObject).toString());
                                    flag = "update";
                                } else {
                                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>执行新增方法:{}", pname);
                                    //3.32-2、新增,如果主表中的安置帮教犯罪编号为空，说明是新数据，或者之前尝试插入安置帮教系统失败
                                    result = dataTransmissionService.savePreArrangedPersonnel(JSONObject.toJSON(jsonObject).toString());
                                    flag = "insert";
                                }
                                //3.33、根据返回结果，进行后续处理
                                if (Constants.Number.NUM_ONE.equals(result.getCode())) {
                                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>入库成功,Code:{},pName:{}", result.getCode(), pname);
                                    //3.34-1、成功，修改中间库主表状态state=1，更新各张表的安置帮教犯罪编号
                                    String azbjPrinum = (String) result.getData();
                                    String sql5 = "update DATA_SWAP.PRISONER_MAIN set state='1',azbj_prinum=\'" + azbjPrinum + "\' where pri_num=\'" + prinum + "\'";
                                    String sql51 = "update DATA_SWAP.PRISONER_MAIN set update_state='2',azbj_prinum=\'" + azbjPrinum + "\' where pri_num=\'" + prinum + "\'";
                                    String sql6 = "update DATA_SWAP.PRISONER_FX set azbj_prinum=\'" + azbjPrinum + "\' where pri_num=\'" + prinum + "\'";
                                    String sql7 = "update DATA_SWAP.PRISONER_SR set azbj_prinum=\'" + azbjPrinum + "\' where pri_num=\'" + prinum + "\'";
                                    String sql8 = "update DATA_SWAP.PRISONER_ZN set azbj_prinum=\'" + azbjPrinum + "\' where pri_num=\'" + prinum + "\'";
                                    String sql9 = "update DATA_SWAP.PRISONER_MULTIMEDIA set azbj_prinum=\'" + azbjPrinum + "\' where pri_num =\'" + prinum + "\'";
                                    String sql10 = "INSERT INTO DATA_SWAP.PRISONER_MIDDLE_LOG (id,pri_num, code, create_time, update_time, azbj_prinum)";
                                    StringBuilder sb = new StringBuilder(sql10);
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    String format = simpleDateFormat.format(date1);
                                    sb.append("values (").append("\'").append(IdGen.strid()).append("\',\'").append(prinum).append("\',\'").append("0")
                                      .append("\',to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\'")
                                      .append("),to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\'),\'")
                                      .append(azbjPrinum).append("\'").append(")");
                                    if (StringUtils.equals("insert", flag)) {
                                        oracleJdbcTemplate.execute(sql6);
                                        oracleJdbcTemplate.execute(sql7);
                                        oracleJdbcTemplate.execute(sql8);
                                        oracleJdbcTemplate.execute(sql9);
                                    }
                                    if (StringUtils.equals("update", flag)) {
                                        oracleJdbcTemplate.execute(sql51);
                                    }
                                    oracleJdbcTemplate.execute(sql5);
                                    oracleJdbcTemplate.execute(sb.toString());
                                    log.info("入库成功后将成功的pri_num添加到redis,priNum:{}", prinum);
                                    // 去缓存中移除指定value
                                    stringRedisTemplate.opsForSet().remove(DJ_PRINUMS, prinum);
                                    // 将执行过后的结果存放入redis
                                    stringRedisTemplate.opsForHash().put(format1.format(date1), prinum, prinum);
                                } else {
                                    log.info("入库失败后将成功的pri_num添加到redis,priNum:{}", prinum);
                                    //3.34-2、失败，修改中间库主表状态state=2，把返回错误码，存储到中间库日志表
                                    String code = result.getData().toString();
                                    String sql5 = "update DATA_SWAP.PRISONER_MAIN set state='2' where pri_num=\'" + prinum + "\'";
                                    String sql10 = "INSERT INTO DATA_SWAP.PRISONER_MIDDLE_LOG (id,pri_num, code, create_time, update_time) ";
                                    StringBuilder sb = new StringBuilder(sql10);
                                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                    String format = simpleDateFormat.format(date1);
                                    sb.append("values (").append("\'").append(IdGen.strid()).append("\',\'").append(prinum).append("\',\'").append(code)
                                      .append("\',to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\'")
                                      .append("),to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\')").append(")");
                                    oracleJdbcTemplate.execute(sql5);
                                    oracleJdbcTemplate.execute(sb.toString());
                                    // 去缓存中移除指定value
                                    stringRedisTemplate.opsForSet().remove(DJ_PRINUMS, prinum);
                                    // 将执行过后的结果存放入redis
                                    stringRedisTemplate.opsForHash().put(format1.format(date1), prinum, prinum);
                                }
                            } catch (EmptyResultDataAccessException e) {
                                log.error("请传递接口用户");
                                String sql6 = "update DATA_SWAP.PRISONER_MAIN set state='2' where pri_num=\'" + prinum + "\'";
                                String sql11 = "INSERT INTO DATA_SWAP.PRISONER_MIDDLE_LOG (id,pri_num, code, create_time, update_time) ";
                                StringBuilder sb = new StringBuilder(sql11);
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String format = simpleDateFormat.format(date1);
                                String code = "999888";
                                sb.append("values (").append("\'").append(IdGen.strid()).append("\',\'").append(prinum).append("\',\'").append(code)
                                  .append("\',to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\'")
                                  .append("),to_date(\'").append(format).append("\',").append("\'").append("YYYY-MM-DD HH24:MI:SS").append("\')").append(")");
                                oracleJdbcTemplate.execute(sql6);
                                oracleJdbcTemplate.execute(sb.toString());
                                log.info("找不到用户,将pri_num添加到redis,priNum:{}", prinum);
                                // 去缓存中移除指定value
                                stringRedisTemplate.opsForSet().remove(DJ_PRINUMS, prinum);
                                // 将执行过后的结果存放入redis
                                stringRedisTemplate.opsForHash().put(format1.format(date1), prinum, prinum);
                            } catch (DataAccessException | InvalidKeyException | NoSuchAlgorithmException e) {
                                log.error("发生异常:", e);
                            }
                            long endTime2 = System.currentTimeMillis();
                            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>处理1条记录,执行耗时,耗时:{}", (endTime2 - startTime2) / 1000 / 60);
                        } catch (Exception e) {
                            log.error("发生异常:{}", e.getMessage());
                        } finally {
                            countDownLatch.countDown();
                            log.error("执行一次,本轮还剩{}", countDownLatch.getCount());
                        }
                    });
                }
            } catch (Exception e) {
                log.info("发生异常:{}", e.getMessage());
            }

            // 调用await();线程阻塞
            try {
                countDownLatch.await();
            } catch (InterruptedException e) {
                log.error("发生异常:{}", e.getMessage());
            }
            long endTime = System.currentTimeMillis();
            log.info("\n>>>>>>>>>>>ending......从中间库同步预安置人员数据<<<<<<<<<<耗时:{}", (endTime - startTime) / 1000 / 60);
        } else {
            log.error("检测到数据源不可用,中间库对接Oracle版本任务失败!");
        }
    }
}